<?php
/**
 * Created by PhpStorm.
 * User: jjj
 * Date: 2017/9/25
 * Time: 11:39
 */

namespace app\admin\command;


use Exception;
use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\input\Option;
use think\console\Output;
use think\Db;

/**
 * 针对数据版本迁移开发的迁移工具命令
 * 不具有通用性，只用于本项目
 *
 * Class Migrate
 * @package app\admin\command
 *
 */
class Migrate extends Command
{
    private $dbScriptDir = APP_ROOT.'/DB/';

    protected function configure()
    {

        $this->setName('migrate')->setDescription('数据库版本迁移工具，默认文件放在DB下面，文件名格式：update_[ver].sql');

        $this->addArgument('file', Argument::OPTIONAL);
        $this->addOption('skip_failed',Option::VALUE_OPTIONAL);
    }

    protected function execute(Input $input, Output $output)
    {
        $file = $input->getArgument('file');
        if (empty($file)) {
            $this->exeMigrate($input, $output);
        }

    }

    private function exeMigrate(Input $input, Output $output) {

        // 检查或创建迁移记录表
        $this->migrateTable();

        $skip_failed = $input->getOption('skip_failed');

        // 读取文件记录
        $files = $this->getFiles();
        // 读取迁移记录
        $records = $this->getRecord();

        $output->writeln('开始执行迁移');
        $num = 0;
        foreach ($files as $v => $f) {

            if ($skip_failed) {
                // 跳过已执行的脚本
                if (isset($records[$v])) continue;
            } else {
                // 跳过成功的脚本
                if (isset($records[$v]) && $records[$v]['success']) continue;
            }

            $this->runScriptFile($v, $f, $input, $output);
            $num++;
        }
        if ($num == 0) {
            $output->writeln('没有新的迁移可执行。');
        }
    }

    /**
     * 创建迁移记录表，如果已经创建则忽略
     */
    private function migrateTable() {

        $sql = <<<SQL
CREATE TABLE IF NOT EXISTS migration (
  id            BIGSERIAL PRIMARY KEY,
  ver           VARCHAR(255) NOT NULL UNIQUE,
  sql_file      VARCHAR(255) NOT NULL UNIQUE,
  success       BOOLEAN NOT NULL ,
  exe_time      TIMESTAMP DEFAULT now(),
  error_message TEXT
);
COMMENT ON TABLE migration IS '数据库版本迁移';
COMMENT ON COLUMN migration.id IS '记录id';
COMMENT ON COLUMN migration.ver IS '版本';
COMMENT ON COLUMN migration.sql_file IS '执行sql文件名';
COMMENT ON COLUMN migration.exe_time IS '执行时间';
COMMENT ON COLUMN migration.error_message IS '错误信息';
SQL;
        $sqlArr = explode(';', $sql);
        $sqlArr = array_filter($sqlArr);
        foreach ($sqlArr as $s) {
            Db::execute($s);
        }

    }

    /**
     * 保存迁移记录
     *
     * @param $ver
     * @param $sql_file
     * @param int $success
     * @param string $err_msg
     */
    private function saveRecord($ver, $sql_file, $success = 1, $err_msg = '') {

        $params = [
            'ver' => $ver,
            'success' => $success,
            'err_msg' => $err_msg,
        ];

        $re = Db::query('SELECT exists(SELECT 1 FROM migration WHERE ver=:ver) e', ['ver' => $ver]);
        $re = $re[0]['e'] ?? false;
        if ($re) {
            $sql = <<<SQL
UPDATE migration SET success=:success, error_message=:err_msg WHERE ver=:ver
SQL;
        } else {
            $sql = <<<SQL
INSERT INTO migration (ver, sql_file, success, error_message) 
VALUES (:ver, :sql_file, :success, :err_msg)
SQL;
            $params['sql_file'] = $sql_file;
        }
        Db::execute($sql, $params);

    }

    /**
     * 获取迁移记录
     *
     * @return array
     */
    private function getRecord() {
        $sql = <<<SQL
SELECT * FROM migration 
ORDER BY id DESC 
SQL;
        $re = Db::query($sql);
        $records = [];
        foreach ($re as $item) {
            $records[$item['ver']] = $item;
        }

        return $records;
    }

    /**
     * 获取迁移脚本文件列表，文件名格式： update_[ver].sql
     *
     * @return array
     */
    private function getFiles() {

        $dir = opendir($this->dbScriptDir);

        $prefix = 'update_';
        $files = [];
        while (($f = readdir($dir)) !== false) {
            if ('.' == $f || '..' == $f) continue;
            if (strpos($f, $prefix) !== 0) continue;

            $_s = substr($f, strpos($f,'_') + 1);
            $_a = explode('.', $_s);
            if (count($_a) < 2) continue;
            $ver = $_a[0];

            $files[$ver] = $f;
        }

        ksort($files);

        return $files;
    }

    /**
     * 执行迁移文件
     *
     * @param $file
     */
    private function execFile($file) {

        // 获取脚本内容
        $file = fopen($this->dbScriptDir . $file,'r');
        $content = '';
        while (($line = fgets($file)) !== false) {
            $line = preg_replace('/[\r\n]+/', "", $line);
            if (empty($line)) continue;
            if (substr($line,0,2) == '--') continue;
            $content .= ' '.$line;
        }

        // 按分号分割
        $scripts = array_filter(explode(';', $content));
        // 循环执行
        foreach ($scripts as $script) {
            Db::query($script);
        }

    }

    private function runScriptFile($ver, $file, Input $input, Output $output) {

        try{
            // PostgreSQL的DDL可以开启事务
            Db::startTrans();

            // 执行文件
            $this->execFile($file);
            // 执行成功保存记录
            $this->saveRecord($ver, $file, 1);

            Db::commit();

            $output->writeln("执行脚本： $file, 成功！");

        }catch (\Exception $ex){

            Db::rollback();
            // 执行出错也记录，但保存错误信息
            $this->saveRecord($ver, $file, 0, json_encode([
                'message' => $ex->getMessage(),
                'trace' => $ex->getTraceAsString(),
            ], JSON_UNESCAPED_UNICODE));
            // 打印错误
            $msg = $ex->getMessage();
            $output->writeln("执行脚本： {$file}；失败信息：($msg)");
            throw $ex;
        }

    }

}